/**
 * 
 */
package extend.org.compiere.hrm;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.DB;

/**
 * @author A.Yerzhanov
 * 24.10.2012
 */

public class IndexSalary {
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (IndexSalary.class);
	
	
	private BigDecimal totalind;
	private int workdays;
	
	public BigDecimal getTotalind() {
		return totalind;
	}

	public void setTotalind(BigDecimal totalind) {
		this.totalind = totalind;
	}

	public int getWorkdays() {
		return workdays;
	}

	public void setWorkdays(int workdays) {
		this.workdays = workdays;
	}

	public static IndexSalary[] getSumAmout(int C_BPartner_ID, Timestamp sdt, Timestamp edt){
	
		if(C_BPartner_ID == 0)
		return null;	
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		IndexSalary result = new IndexSalary();
				
		ArrayList<IndexSalary> list = new ArrayList<IndexSalary>();		
				
		// 
		String sql_ = " WITH T1 AS ( \n " +
				      " SELECT MAX(TOTALIND) AS TOTALIND, MAX(IND) AS IND, WORKDAYS \n " +
				      " FROM HRM_AVERAGELINE_V \n " +
					  " WHERE C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					  " AND DATEFROM BETWEEN " + DB.TO_DATE(sdt) + " AND " + DB.TO_DATE(edt) + " \n " +
				      " GROUP BY AD_CLIENT_ID, AD_ORG_ID, C_BPARTNER_ID, DATEFROM, WORKDAYS \n " +
				      " ORDER BY DATEFROM \n ) \n \n " +
				      " SELECT COALESCE(ROUND(SUM(TOTALIND),3),0), COALESCE(SUM(WORKDAYS), 0::numeric) FROM T1;";
					  			
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();	
			while(rs.next())
			{
				result = new IndexSalary();
				result.setTotalind(rs.getBigDecimal(1));
				result.setWorkdays(rs.getInt(2));
				list.add(result);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new IndexSalary[list.size()]);
	}
	
	public static BigDecimal getSumBonus(int C_BPartner_ID, Timestamp edt){
		
		BigDecimal result = new BigDecimal(0);
		
		if(C_BPartner_ID == 0)
			return result;	
		    
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	
		// 
		String sql_ = " SELECT COALESCE(ROUND(SUM(TOTALINDSEQ) * MIN(IND),3),0) \n " +
						" FROM HRM_AVERAGEBONUS_SUM(" + DB.TO_DATE(edt, true) + "::date) \n " +
						" WHERE C_BPARTNER_ID=" + C_BPartner_ID;			
		try
		{
			pstmt = DB.prepareStatement(sql_,null);		
			rs = pstmt.executeQuery();	
			if(rs.next())
				return rs.getBigDecimal(1);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return result;
		}

}